package cz.acies.organizer.data;

import java.sql.Connection;
import java.sql.Types;

import cz.acies.data.DBUpadaterAsbtract;
import cz.acies.face.modul.ModulViewer;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.sql.UpdateQuery;
import cz.acies.uniql.sql.element.UniQLCondition;

public class DBUpdater extends DBUpadaterAsbtract
{
	public DBUpdater() {
		super();
	}

	public DBUpdater(Connection conn, DBMetaData dbmd) {
		super(conn, dbmd);
	}

	@Override
	public void updateStructure() throws Exception {
		//System.out.println("DBUpdater.updateStructure()");
		String table = "";
		
		table = "mail_address";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "mailAddress", Types.VARCHAR, 128, 0, false, "", true, "", conn);
		}

		table = "user_repository";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "CHANGE", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "userId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "label", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "image", Types.VARCHAR, 128, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "path", Types.VARCHAR, 255, 0, false, "/", true, "", conn);
			dbmd.updateColumn(table, "path", Types.INTEGER, 11, 0, false, "0", true, "", conn);
		}
		table = "user_repository_record";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "CHANGE", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "fileName", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "fileExt", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		
		// -----  -------------------------------------------------------------
		
		table = "calendar_task";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "CHANGE", conn);
			dbmd.updateColumn(table, "userId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "folderId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "subject", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "categories", Types.VARCHAR, 255, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "description", Types.VARCHAR, 255, 0, false, "", false, "", conn);
			dbmd.updateColumn(table, "location", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "dateStart", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "dateEnd", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "duration", Types.INTEGER, 11, 0, false, "0", false, "", conn);
			dbmd.updateColumn(table, "allDay", Types.TINYINT, 1, 0, false, "0", false, "", conn);			
			dbmd.updateColumn(table, "recType", Types.TINYINT, 1, 0, false, "-1", false, "", conn);
			dbmd.updateColumn(table, "recInterval", Types.INTEGER, 11, 0, false, "0", false, "", conn);
			dbmd.updateColumn(table, "recDayOfMonth", Types.SMALLINT, 6, 0, false, "0", false, "", conn);
			dbmd.updateColumn(table, "recDayOfWeekMask", Types.INTEGER, 11, 0, false, "0", false, "", conn);			
			dbmd.updateColumn(table, "recInstance", Types.SMALLINT, 6, 0, false, "0", false, "", conn);
			dbmd.updateColumn(table, "recStartDate", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "recNoEndDate", Types.SMALLINT, 6, 0, false, "0", false, "", conn);
			dbmd.updateColumn(table, "recEndDate", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			//dbmd.updateColumn(table, "recRule", Types.VARCHAR, 4096, 0, false, "", false, "", conn);
		}
		
		table = "calendar_recurrence_exceptions";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "CHANGE", conn);
			dbmd.updateColumn(table, "calendarId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "dateStart", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "dateEnd", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "childId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		
		// -----  -------------------------------------------------------------

		table = "bank";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "name", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "code", Types.VARCHAR, 4, 0, false, "", true, "", conn);
		}
		/*
		INSERT INTO `bank` (`id`, `writerId`, `writeDate`, `editorId`, `editDate`, `name`, `code`) VALUES 
		  ('BN.X.000000000000100', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'Kpmerčmí Banka', '0100'),
		  ('BN.X.000000000000800', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'Česká spořitelna', '0800');
		 */
		table = "bank_account";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "BX.X.00000000000ROOT", true, "", conn);
			dbmd.updateColumn(table, "header", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "prefix", Types.VARCHAR, 6, 0, false, "000000", true, "", conn);
			dbmd.updateColumn(table, "number", Types.VARCHAR, 22, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "code", Types.VARCHAR, 4, 0, false, "0000", true, "", conn);
			dbmd.updateColumn(table, "iban", Types.VARCHAR, 50, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "currency", Types.VARCHAR, 3, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "descript", Types.VARCHAR, 4096, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "typeId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "ownerId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}

		/*
		INSERT INTO `bank_account` (`id`, `writerId`, `writeDate`, `editorId`, `editDate`, `parentId`, `header`, `prefix`, `number`, `code`, `iban`, `currency`, `descript`, `typeId`, `ownerId`) VALUES 
		  ('BN.A.KB0000000000001', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'BN.X.000000000000100', 'Běžný účet', '000051', '5845250207', '0100', 'CZ3001000000515845250207', 'CZK', '', ' ', 'WX.R.AEFBE4JE41ZYBEM'),
		  ('BN.A.KB0000000000002', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'BN.X.000000000000100', 'Hypoteka', '000051', '1096181727', '0100', 'CZ6701000000511096181727', 'CZK', ' ', ' ', 'WX.R.AEFBE4JE41ZYBEM'),
		  ('BN.A.000000000000801', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'BX.X.000000000000800', 'Běžný účet', '000000', '1539964043', '0800', 'CZ4508000000001539964043 ', 'CZK', ' ', '', 'WX.R.AEFBE4JE41ZYBEM'),
		  ('BN.A.000000000000002', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'WX.L.0000000000ADMIN', '1900-01-01 00:00:01', 'BN.X.000000000000800', 'Slevový účet', '000000', '2509940053 ', '0800', '', 'CZK', '', ' ', ' ');
		*/
		
		/*
		CREATE TABLE `bank_account` (
		  `UID` VARCHAR(16) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
		  `PARENT_ID` VARCHAR(16) COLLATE utf8_general_ci NOT NULL DEFAULT 'BX-X-0000000ROOT',
		  `HEADER` VARCHAR(16) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
		  `PREFIX` VARCHAR(6) COLLATE utf8_general_ci NOT NULL DEFAULT '000000',
		  `NUMBER` VARCHAR(22) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
		  `CODE` VARCHAR(4) COLLATE utf8_general_ci NOT NULL DEFAULT '0000',
		  `IBAN` VARCHAR(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
		  `CURRENCY` VARCHAR(3) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
		  `DESCRIPT` TEXT COLLATE utf8_czech_ci,
		  `TYPE_ID` VARCHAR(20) COLLATE utf8_general_ci NOT NULL DEFAULT '',
		  `OWNER_ID` VARCHAR(20) COLLATE utf8_general_ci NOT NULL DEFAULT '',
		  `WRITER_ID` VARCHAR(16) COLLATE utf8_czech_ci NOT NULL DEFAULT 'W_ADMIN',
		  `WRITE_DATE` DATETIME DEFAULT '1900-01-01 00:00:00',
		  `EDITOR_ID` VARCHAR(16) COLLATE utf8_czech_ci NOT NULL DEFAULT 'W_ADMIN',
		  `EDIT_DATE` DATETIME DEFAULT '1900-01-01 00:00:00',
		  PRIMARY KEY (`UID`),
		  UNIQUE KEY `NUMBER` (`PREFIX`, `NUMBER`, `CODE`),
		  KEY `PARENT_ID` (`PARENT_ID`),
		  KEY `CODE` (`CODE`)
		*/
		table = "bank_balance";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "accountId", Types.VARCHAR, 20, 0, false, "", true, "CHANGE", conn);
			dbmd.updateColumn(table, "dateFrom", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "dateTo", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "balanceStart", Types.DOUBLE, 15, 2, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "balanceEnd", Types.DOUBLE, 15, 2, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "turnoverFor", Types.DOUBLE, 15, 2, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "turnoverTo", Types.DOUBLE, 15, 2, false, "0.0", true, "", conn);
		}

		table = "bank_transaction";
		if (createModulAuditTable(""+table)) {
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "bankAccount", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "referNumber", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "dateAccounting", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "dateEntry", Types.TIMESTAMP, 128, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "dateMaturity", Types.TIMESTAMP, 128, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(table, "amountValue", Types.DOUBLE, 15, 2, false, "0.0", true, "", conn);
			dbmd.updateColumn(table, "varSymb", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "constSymb", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "specSymb", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "storno", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "description", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "descriptionRecipient", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "descriptionSystem", Types.VARCHAR, 255, 0, false, "", true, "", conn);
		}
		
	}

	@Override
	public void updateData() throws Exception {
		
		createModul("LX.G.CONTACT", "LX.M.CONTACT", "Kontakty", "icon-contact", "contact_modul.jsp");
		createModul("LX.G.CALENDAR", "LX.M.CALENDAR", "Kalendář", "icon-calendar", "");
		createModul("LX.G.EMAIL", "LX.M.EMAIL", "Pošta", "icon-email", "");
		createModul("LX.G.REPOSITORY", "LX.M.REPOSITORY", "Disk", "", "");		
		createModul("LX.G.BANK_ACCOUNTS", "LX.M.BANK_ACCOUNTS", "Bankovní účty", "icon-banking", "bank_modul.jsp");
		createModul("LX.G.USER", "LX.M.USER", "Užívatel", "", "user_modul.jsp");
		
		createModulMenu("MN.G.000000000000CRM", "", "CRM", "", 1, "");
		createModulMenu("MN.G.000000000000004", "MN.G.000000000000CRM", "Kontakty", "icon-contact", 1, "LX.M.CONTACT");
		createModulMenu("MN.G.000000000000005", "MN.G.000000000000CRM", "Kalendář", "icon-calendar", 2, "LX.M.CALENDAR");
		createModulMenu("MN.G.000000000000006", "MN.G.000000000000CRM", "Pošta", "icon-email", 3, "LX.M.EMAIL");
		createModulMenu("MN.G.000000000000007", "MN.G.000000000000CRM", "Disk", "", 4, "LX.M.REPOSITORY");		
		createModulMenu("MN.G.00000000000BANK", "", "Banka", "", 2, "");
		createModulMenu("MN.G.000000000000001", "MN.G.00000000000BANK", "Bankovní účty", "icon-banking", 1, "LX.M.BANK_ACCOUNTS");
		//createModulMenu("MN.G.000000000000002", "MN.G.00000000000BANK", "Bankovní účty - předledy", "icon-banking", 2, "LX.M.BANK_HISTORY");
		createModulMenu("MN.G.00000000SETTING", "", "Nastavení", "", 3, "");
		createModulMenu("MN.G.000000000000003", "MN.G.00000000SETTING", "Užívatel", "", 1, "LX.M.USER");
		
		//if (!updates.contains("900.000.001")) {
			String tableName = "gui_modul";
			String pckg = "cz.acies.organizer.face.modul.";
			ModulViewer viewer = new ModulViewer("LX.G.CONTACT", "Kontakty", "icon-contact");
			if (recordExists(""+tableName, viewer.getId())) {
				UpdateQuery sql = new UpdateQuery(dbmd())
					.addFrom(""+tableName)
					.addParam("pageUrl", "")
					.addParam("treeViewer", pckg+"ContactFolderView")
					.addParam("tableViewer", pckg+"ContactRecordView")
					.add(new UniQLCondition("id", "=", "'"+viewer.getId()+"'"));
				qr.update(sql.toSQLString());
			} else {
				
			}
			viewer = new ModulViewer("LX.G.REPOSITORY", "Disk", "");
			if (recordExists(""+tableName, viewer.getId())) {
				UpdateQuery sql = new UpdateQuery(dbmd())
					.addFrom(""+tableName)
					.addParam("pageUrl", "")
					.addParam("treeViewer", pckg+"RepositoryFolderView")
					.addParam("tableViewer", pckg+"RepositoryRecordView")
					.add(new UniQLCondition("id", "=", "'"+viewer.getId()+"'"));
				qr.update(sql.toSQLString());
			} else {
				
			}
			viewer = new ModulViewer("LX.G.BANK_ACCOUNTS", "Bankovní účty", "");
			if (recordExists(""+tableName, viewer.getId())) {
				UpdateQuery sql = new UpdateQuery(dbmd())
					.addFrom(""+tableName)
					.addParam("pageUrl", "")
					.addParam("treeViewer", pckg+"BankFolderView")
					.addParam("tableViewer", pckg+"BankRecordView")
					.add(new UniQLCondition("id", "=", "'"+viewer.getId()+"'"));
				qr.update(sql.toSQLString());
			} else {
				
			}
			viewer = new ModulViewer("LX.G.USER", "Užívatel", "");
			if (recordExists(""+tableName, viewer.getId())) {
				UpdateQuery sql = new UpdateQuery(dbmd())
					.addFrom(""+tableName)
					.addParam("pageUrl", "")
					.addParam("treeViewer", pckg+"UserFolderView")
					.addParam("tableViewer", pckg+"UserRecordView")
					.add(new UniQLCondition("id", "=", "'"+viewer.getId()+"'"));
				qr.update(sql.toSQLString());
			} else {
				
			}
			
		//}
		/*
		INSERT INTO `gui_modul_menu` (`id`, `parentId`, `label`, `image`, `priority`, `modulId`) VALUES 
		  ('MN.G.000000000000CRM', '', 'CRM', '', 1, ''),
		  ('MN.G.000000000000004', 'MN.G.000000000000CRM', 'Kontakty', 'icon-contact', 1, 'LX.M.CONTACT'),
		  ('MN.G.000000000000005', 'MN.G.000000000000CRM', 'Kalendář', 'icon-calendar', 2, 'LX.M.CALENDAR'),
		  ('MN.G.000000000000006', 'MN.G.000000000000CRM', 'Pošta', 'icon-email', 3, 'LX.M.EMAIL'),
		  ('MN.G.000000000000007', 'MN.G.000000000000CRM', 'Disk', '', 4, 'LX.M.REPOSITORY'),
		  ('MN.G.00000000000BANK', '', 'Banka', '', 2, ''),
		  ('MN.G.000000000000001', 'MN.G.00000000000BANK', 'Bankovní účty', 'icon-banking', 1, 'LX.M.BANK_ACCOUNTS'),
		  ('MN.G.00000000SETTING', '', 'Nastavení', '', 3, ''),
		  ('MN.G.000000000000003', 'MN.G.00000000SETTING', 'Užívatel', '', 1, 'LX.M.USER');
		*/
		
		/*
		INSERT INTO `gui_user_modul_define` (`UID`, `USER_ID`, `MODUL_ID`, `OBJECT_ID`, `PARENT_ID`, `PARAM_KEY`, `PARAM_VALUE`, `CONTENT`) VALUES 
		  ('W_ADMIN_LX.M.CONTACT', 'W_ADMIN', 'LX.M.CONTACT', '', '', '', '', '<USER><MODUL><LIST_VIEW><COLUMN_DEFINE id=\"firstName\" caption=\"Jméno\" width=\"200\" alignment=\"16384\" pattern=\"\"/><COLUMN_DEFINE id=\"lastName\" caption=\"Příjmení\" width=\"200\" alignment=\"16384\" pattern=\"\"/><COLUMN_DEFINE id=\"title\" caption=\"Titul\" width=\"100\" alignment=\"16384\" pattern=\"\"/><COLUMN_DEFINE id=\"writeDate\" caption=\"Datum nar.\" width=\"100\" alignment=\"16348\" pattern=\"\"/><COLUMN_DEFINE id=\"editorId\" caption=\"Opravil\" width=\"100\" alignment=\"16384\" pattern=\"\"/></LIST_VIEW></MODUL></USER>');

		INSERT INTO `user_person` (`id`, `firstName`, `lastName`, `mailId`) VALUES 
		  ('WX.R.AEFBE4JE41ZYBEM', '', '', NULL),
		  ('WX.R.V0IGPTNDYR0MPYN', '', '', NULL),
		  ('WX.R.4YM7IBO7FLRNXBT', NULL, NULL, NULL);
		  
		INSERT INTO `user_login` (`id`, `userName`, `userPassword`, `personId`) VALUES 
		  ('WX.L.5RHPF3REIIU8RTR', 'petr', 'p.Zeli75', 'WX.R.V0IGPTNDYR0MPYN'),
		  ('WX.L.A8AATB5LCJK2VDJ', 'zdenek', 'z.Dyt71', 'WX.R.9MKQOXGCTJECZ1B'),
		  ('WX.L.GZ69V88D4ZWFPF8', 'jarda', 'j.Dyt68', 'WX.R.AEFBE4JE41ZYBEM');

		*/
	}

}
